package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.DateUtil;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.erp.ErpGoodsStockInfoItemEntity;
import com.b2c.entity.ErpGoodsStockLossFormEntity;
import com.b2c.entity.ErpGoodsStockLossItemEntity;
import com.b2c.entity.erp.vo.ErpGoodsBadStockLogVo;
import com.b2c.entity.erp.vo.ErpGoodsBadStockVo;
import com.b2c.entity.erp.vo.ErpGoodsStockLossFormVo;
import com.b2c.entity.erp.vo.ErpGoodsStockLossItemVo;
import com.b2c.repository.Tables;
import com.b2c.repository.utils.OrderNumberUtils;
import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;
import com.b2c.entity.enums.erp.EnumGoodsStockLogType;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class StockBadRepository {
    @Autowired
    JdbcTemplate jdbcTemplate;

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 查询不良品列表
     *
     * @param pageIndex
     * @param pageSize
     * @param number
     * @return
     */
    public PagingResponse<ErpGoodsBadStockVo> getStockBadList(Integer pageIndex, Integer pageSize, String number, Integer type) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.*,B.`name` as goodsName,B.number as goodsNumber,C.color_value,C.size_value ");
        sb.append(" FROM ").append(Tables.ErpGoodsBadStock).append(" A ");
        sb.append(" LEFT JOIN ").append(Tables.ErpGoods).append(" B ON A.goodsId=B.id ");
        sb.append(" LEFT JOIN ").append(Tables.ErpGoodsSpec).append(" C ON A.specId=C.id ");
        sb.append("WHERE A.isDelete=0 ");

        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(number)) {
            sb.append("AND A.specNumber = ? ");
            params.add(number);
        }
        if (type != null) {
            sb.append(" AND A.type=?");
            params.add(type);
        }

        sb.append(" ORDER BY A.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<ErpGoodsBadStockVo> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsBadStockVo.class), params.toArray(new Object[params.size()]));

        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }

    /**
     * 根据Id查询
     *
     * @param stockId
     * @return
     */
    public ErpGoodsBadStockVo getStockBad(Integer stockId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS SQL_CALC_FOUND_ROWS A.*,B.`name` as goodsName ");
        sb.append(" FROM ").append(Tables.ErpGoodsBadStock).append(" A LEFT JOIN ").append(Tables.ErpGoods).append(" B ON A.goodsId=B.id ");
        sb.append("WHERE A.id=? ");
        return jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsBadStockVo.class), stockId);
    }

    /**
     * 根据Id查询不良品详情
     *
     * @param stockId
     * @return
     */
    public List<ErpGoodsBadStockLogVo> getBadLog(Integer stockId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.id,A.quantity,A.remark,A.createTime,A.type,B.specNumber,B.color_value,B.size_value,B.style_value,C.`name` locationName,D.`name` ");
        sb.append("FROM ").append(Tables.ErpGoodsBadStockLog).append(" A LEFT JOIN ").append(Tables.ErpGoodsSpec).append(" B ON A.specId=B.id ");
        sb.append("LEFT JOIN ").append(Tables.ErpStockLocation).append(" C ON C.id=A.locationId LEFT JOIN ").append(Tables.ErpGoods).append(" D ON D.id=B.goodsId ");
        sb.append("WHERE A.stockId=? ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsBadStockLogVo.class), stockId);
    }

    /**
     * 关键词搜索不良商品
     *
     * @return
     */
    public List<ErpGoodsBadStockLogVo> getLIKEBad(String number) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.id,A.specNumber,A.currentQty,B.`name`,C.color_value,C.size_value,D.`name` locationName ");
        sb.append("FROM ").append(Tables.ErpGoodsStockInfo);
        sb.append(" A LEFT JOIN ");
        sb.append(Tables.ErpGoods).append(" B ON B.id=A.goodsId ");
        sb.append("LEFT JOIN ").append(Tables.ErpGoodsSpec).append("C ON C.specNumber=A.specNumber ");
        sb.append("LEFT JOIN ").append(Tables.ErpStockLocation).append(" D ON D.id=A.locationId ");
        sb.append("WHERE  A.isDelete=0 AND A.specNumber like '%").append(number).append("%' ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsBadStockLogVo.class));
    }

    /**
     * 新增报损单
     *
     * @param userId
     * @param userName
     * @param lossText
     * @param billNo
     * @param billDate
     * @param specNumbers
     * @param quantities
     * @param locationNames
     */
    @Transactional
    public void lossAdd(Integer userId, String userName, String lossText, String billNo, String billDate, String[] specNumbers, String[] quantities, String[] locationNames,String[] remark) {
        Integer quant = 0;
        for (int i = 0; i < quantities.length; i++) {
            if (!StringUtils.isEmpty(specNumbers[i])) {
                quant = Integer.parseInt(quantities[i]) + quant;
            }
        }
        final int quanty = quant;
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO ").append(Tables.ErpGoodsStockLossForm).append(" set number=?,totalQuantity=?,createTime=?,createUserId=?,createUserName=?,sourceType=1,status=0,lossReason=?");
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sb.toString(), Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, billNo);
                ps.setInt(2, quanty);
                ps.setLong(3, DateUtil.strToLong(billDate));
                ps.setInt(4, userId);
                ps.setString(5, userName);
                ps.setString(6, lossText);
                return ps;
            }
        }, keyHolder);

        Integer formId = keyHolder.getKey().intValue();

        for (int i = 0; i < quantities.length; i++) {
            if (!StringUtils.isEmpty(specNumbers[i])) {
                Integer spcId = jdbcTemplate.queryForObject("select id from erp_goods_spec where specNumber=?", Integer.class, specNumbers[i]);
                Integer goodsId = jdbcTemplate.queryForObject("select goodsId from erp_goods_spec where specNumber=?", Integer.class, specNumbers[i]);
                Integer locationId = jdbcTemplate.queryForObject("select id from erp_stock_location where name=?", Integer.class, locationNames[i]);

                StringBuffer sbu = new StringBuffer();
                sbu.append("INSERT INTO ").append(Tables.ErpGoodsStockLossItem).append(" set formId=?,goodsId=?,specId=?,specNumber=?,quantity=?,sourceLocationId=?,status=0,remark=?");
                jdbcTemplate.update(sbu.toString(), formId, goodsId, spcId, specNumbers[i], Integer.parseInt(quantities[i]), locationId,remark[i]);
            }
        }
    }


    /**
     * 根据仓位和规格编号查询仓位和数量是否存在
     *
     * @return
     */
    public ErpGoodsStockInfoEntity getLocationAndQuanty(String LocationName, String specNumber) {
        try {
            Integer locationId = jdbcTemplate.queryForObject("SELECT id FROM erp_stock_location WHERE `name`= ? ", Integer.class, LocationName);
            return jdbcTemplate.queryForObject("select * from erp_goods_stock_info where locationId=? and specNumber=? and isDelete=0", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), locationId, specNumber);
        } catch (Exception e) {
            return null;
        }

    }

    /**
     * 查询报损单详情
     *
     * @param pageIndex
     * @param pageSize
     * @param number
     * @param startTime
     * @param endTime
     * @return
     */
    public PagingResponse<ErpGoodsStockLossFormEntity> getLossFromList(Integer pageIndex, Integer pageSize, String number, Long startTime, Long endTime) {
        StringBuffer sb = new StringBuffer().append("SELECT SQL_CALC_FOUND_ROWS * FROM erp_goods_stock_loss_form where 1=1 ");
        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(number)) {
            sb.append("AND number = ? ");
            params.add(number);
        }
        if (startTime > 0) {
            sb.append(" AND createTime >= ?");
            params.add(startTime);
        }
        if (endTime > 0) {
            sb.append(" AND createTime < ? ");
            params.add(endTime);
        }

        sb.append(" ORDER BY id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<ErpGoodsStockLossFormEntity> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsStockLossFormEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }

    /**
     * 根据Id获取报损单详情
     *
     * @param formId
     * @return
     */
    public ErpGoodsStockLossFormVo getLossItem(Integer formId) {
        StringBuffer sb = new StringBuffer().append("SELECT SQL_CALC_FOUND_ROWS id,number,createTime,createUserName FROM erp_goods_stock_loss_form WHERE id = ?");
        ErpGoodsStockLossFormVo item = jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsStockLossFormVo.class), formId);

        StringBuffer sk = new StringBuffer();
        sk.append("SELECT SQL_CALC_FOUND_ROWS A.id,A.specNumber,A.quantity,A.remark,B.`name`,C.color_value,C.size_value,D.`name` locationName ");
        sk.append("FROM ").append(Tables.ErpGoodsStockLossItem).append(" A LEFT JOIN ").append(Tables.ErpGoods).append(" B ON B.id=A.goodsId LEFT JOIN ");
        sk.append(Tables.ErpGoodsSpec).append(" C ON C.id=A.specId LEFT JOIN ").append(Tables.ErpStockLocation).append(" D ON D.id=A.sourceLocationId ");
        sk.append(" where A.formId=? ");
        item.setItems(jdbcTemplate.query(sk.toString(), new BeanPropertyRowMapper<>(ErpGoodsStockLossItemVo.class), item.getId()));
        return item;
    }

    /**
     * 报损单审核
     *
     * @param id
     * @param userId
     * @param userName
     * @return
     */
    @Transactional
    public Integer checkLoss(Integer id, Integer userId, String userName) {
        ErpGoodsStockLossFormEntity formEntity = null;
        try {
            formEntity = jdbcTemplate.queryForObject("select * from erp_goods_stock_loss_form where id=? ", new BeanPropertyRowMapper<>(ErpGoodsStockLossFormEntity.class), id);
            if (formEntity.getStatus() != 0) return -3;//
        } catch (Exception e) {
            return -2;//查不到数据
        }
        List<ErpGoodsStockLossItemEntity> lists = jdbcTemplate.query("SELECT  * FROM " + Tables.ErpGoodsStockLossItem + " WHERE formId=?",
                new BeanPropertyRowMapper<>(ErpGoodsStockLossItemEntity.class), id);
        if(lists==null || lists.size() ==0) return -2;

        try {
            /********更新报损单状态*********/
            jdbcTemplate.update("update " + Tables.ErpGoodsStockLossForm + " set auditTime=?,auditUserId=?,auditUserName=?,status=1 where id=?",
                    System.currentTimeMillis() / 1000, userId, userName, id);


            /*********处理报损数据（减库存、加出库记录）**********/


            /*********添加出库数据*erp_stock_out_form*********/
            String stockOutFormSQL = "INSERT INTO " + Tables.ErpStockOutForm + " (stockOutNo,status,printStatus,printTime,createTime,createBy,modifyTime,completeTime,stockOutUserId,stockOutUserName,outType) " +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?)";

            String outNum = "OUT-PAN" + OrderNumberUtils.getOrderIdByMinute();
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(stockOutFormSQL, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, outNum);
                    ps.setInt(2, 3);
                    ps.setInt(3, 0);
                    ps.setLong(4, 0);
                    ps.setLong(5, System.currentTimeMillis() / 1000);
                    ps.setString(6, userName);
                    ps.setLong(7, System.currentTimeMillis() / 1000);
                    ps.setLong(8, System.currentTimeMillis() / 1000);
                    ps.setInt(9, userId);
                    ps.setString(10, userName);
                    ps.setInt(11, 4);//出库类型1订单拣货出库2采购退货出库3盘点出库4报损出库
                    return ps;
                }
            }, keyHolder);

            Long outFormId = keyHolder.getKey().longValue();


            for (ErpGoodsStockLossItemEntity item : lists) {
                //更新报损items
                jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockLossItem + " SET status=1 where id=?", item.getId());

                /*****更新库存信息*****/
                ErpGoodsStockInfoEntity stockInfo = null;
//                Long currentQty = 0L;
                try {
//                    currentQty = jdbcTemplate.queryForObject("SELECT currentQty FROM erp_goods_stock_info WHERE locationId=? AND specId=? and isDelete=0", Long.class, item.getSourceLocationId(), item.getSpecId());
                    stockInfo = jdbcTemplate.queryForObject("SELECT * FROM erp_goods_stock_info WHERE isDelete=0 AND specId=? AND locationId=?",new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),item.getSpecId(),item.getSourceLocationId());
                } catch (Exception e) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
                    return -1;
                }
                //更新仓位库存信息
                jdbcTemplate.update("update " + Tables.ErpGoodsStockInfo + " set currentQty=? where id=?",
                        stockInfo.getCurrentQty() - item.getQuantity(), stockInfo.getId());


//                Long specQty = jdbcTemplate.queryForObject("SELECT currentQty FROM " + Tables.ErpGoodsSpec + " WHERE id=? and specNumber=?", Long.class, item.getSpecId(), item.getSpecNumber());

//                jdbcTemplate.update("update " + Tables.ErpGoodsSpec + " set currentQty=? where id=?", specQty - item.getQuantity(), item.getSpecId());


                /*********添加出库数据*erp_stock_out_form_item*********/
                String stockOutFormItemSQL = "INSERT INTO erp_stock_out_form_item (formId,itemId,goodsId,specId,locationId,quantity) value (?,?,?,?,?,?)";
                jdbcTemplate.update(stockOutFormItemSQL, outFormId, item.getId(), item.getGoodsId(), item.getSpecId(), item.getSourceLocationId()
                        , item.getQuantity());

                /*******************加入不良品仓库*********************/
                StringBuffer sb = new StringBuffer();
                sb.append("INSERT INTO ").append(Tables.ErpGoodsBadStock).append(" set goodsId=?,specId=?,specNumber=?,type=1,locationId=?,status=0,createTime=?,quantity=?,reason=?,sourceId=?");
                jdbcTemplate.update(sb.toString(),item.getGoodsId(),item.getSpecId(),item.getSpecNumber(),item.getSourceLocationId(),System.currentTimeMillis() / 1000,item.getQuantity(),item.getRemark(),formEntity.getNumber());


                /******添加日志*****/
                String goodsNumber = jdbcTemplate.queryForObject("SELECT number FROM " + Tables.ErpGoods + " WHERE id=? ", String.class, item.getGoodsId());

                String remark = "报损单号 : " + formEntity.getNumber() + " , 报损原因 :" + formEntity.getLossReason();
                String dateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());

                //添加出库日志
                jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockLogs + " SET goodsId=?,goodsNumber=?,specId=?,specNumber=?,locationId=?,quantity=?,type=?,sourceType=?,sourceId=?,createTime=?,createUserId=?,createUserName=?,remark=?,createOn=?,currQty=? ",
                        item.getGoodsId(), goodsNumber, item.getSpecId(), item.getSpecNumber(), item.getSourceLocationId(), item.getQuantity(), EnumGoodsStockLogType.OUT.getIndex()
                        , EnumGoodsStockLogSourceType.LOSS.getIndex(), id, dateTime, userId, userName, remark, System.currentTimeMillis() / 1000,stockInfo.getCurrentQty() - item.getQuantity());

                //更新库存批次信息
                //库存信息


                /******************批次先进先出，所以减少最早批次库存********************/
                var pici = jdbcTemplate.query("SELECT * FROM erp_goods_stock_info_item WHERE stockInfoId=? AND currentQty > 0 ORDER BY id asc limit 1",new BeanPropertyRowMapper<>(ErpGoodsStockInfoItemEntity.class),stockInfo.getId());
                if(pici==null || pici.size()==0){
                    //都没有库存，就重新取最新一条数据
                    pici = jdbcTemplate.query("SELECT * FROM erp_goods_stock_info_item WHERE stockInfoId=? ORDER BY id desc limit 1",new BeanPropertyRowMapper<>(ErpGoodsStockInfoItemEntity.class),stockInfo.getId());
                }
                //更新批次库存 减少
                jdbcTemplate.update("UPDATE erp_goods_stock_info_item SET currentQty=currentQty-? WHERE id=?",item.getQuantity(),pici.get(0).getId());

            }
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
            return -100;
        }
        return 0;

    }
}
